﻿using System;
using System.Collections.Generic;
using System.Text;
using uMES.LeanManufacturing.ParameterDTO;
using uMES.LeanManufacturing.Common;
using System.Data;
using uMES.LeanManufacturing.DBUtility;
using System.Net;

namespace uMES.LeanManufacturing.ReportBusiness
{
    public class uMESCommonBusiness
    {
        /// <summary>
        /// 获取本机IP地址
        /// </summary>
        /// <returns></returns>
        public  string GetIpAddress()
        {
            string hostName = Dns.GetHostName();   //获取本机名
            IPHostEntry localhost = Dns.GetHostByName(hostName);    //方法已过期，可以获取IPv4的地址
            IPAddress localaddr = localhost.AddressList[0];

            return localaddr.ToString();
        }

        public DataTable GetFactoryNamesByFactoryID(string strFactoryID)
        {
            string sql = "select f.factoryname,f.description,f.factoryid,nvl(f.description,f.factoryname) as fName from factory f";
            sql += string.Format(" where f.factoryid = '{0}'", strFactoryID);
            DataTable dt = OracleHelper.GetDataTable(sql);
            return dt;
        }

        public DataTable GetTeamNamesByTeamID(string strTeamID)
        {
            string sql = "select t.teamid,t.teamname,t.factoryid,nvl(f.description,f.factoryname) as fName from team t left join factory f on t.factoryid=f.factoryid";
            sql += string.Format(" where t.teamid = '{0}'", strTeamID);
            DataTable dt = OracleHelper.GetDataTable(sql);
            return dt;
        }
        #region 获取Employee
        public DataTable GetEmployeeByFactory(string strFactoryID)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT e.employeeid,e.employeename,e.fullname");
            strQuery.AppendLine("FROM employee e");
            strQuery.AppendLine("LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid");
            strQuery.AppendLine("WHERE 1 = 1");

            if (strFactoryID != string.Empty)
            {
                strQuery.AppendLine(string.Format("AND sv.factoryid = '{0}'", strFactoryID));
            }

            strQuery.AppendLine("ORDER BY e.employeename ASC");

            return OracleHelper.GetDataTable(strQuery.ToString());
        }

        public DataTable GetEmployee(string strTeamID)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT e.employeeid,e.employeename,e.fullname");
            strQuery.AppendLine("FROM employee e");
            strQuery.AppendLine("WHERE 1 = 1");

            if (strTeamID != string.Empty)
            {
                strQuery.AppendLine(string.Format("AND e.teamid = '{0}'", strTeamID));
            }

            strQuery.AppendLine("ORDER BY e.employeename ASC");

            return OracleHelper.GetDataTable(strQuery.ToString());
        }
        #endregion

        #region 获取Team
        /// <summary>
        /// 获取Team
        /// </summary>
        public DataTable GetTeamByFactoryId(string strFactoryID)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT t.teamid,t.teamname");
            strQuery.AppendLine("FROM team t");
            strQuery.AppendFormat("WHERE t.factoryid = '{0}'", strFactoryID);
            strQuery.AppendLine("ORDER BY t.teamname ASC");
            return OracleHelper.GetDataTable(strQuery.ToString());
        }
        public DataTable GetTeamByTeamId(string strTeamID)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT t.teamid,t.teamname");
            strQuery.AppendLine("FROM team t");
            strQuery.AppendFormat("WHERE t.teamid = '{0}'", strTeamID);
            strQuery.AppendLine("ORDER BY t.teamname ASC");
            return OracleHelper.GetDataTable(strQuery.ToString());
        }
        #endregion

        #region 根据多个WorkflowID获取Spec列表
        /// <summary>
        /// 根据多个WorkflowID获取Spec列表
        /// </summary>
        /// <param name="strWorkflowID"></param>
        /// <returns></returns>
        public DataTable GetSpecListByWorkflowID(List<string> listWorkflowID)
        {
            string strWorkflowIDs = string.Empty;
            for (int i = 0; i < listWorkflowID.Count; i++)
            {
                strWorkflowIDs = strWorkflowIDs + string.Format("'{0}'", listWorkflowID[i]);
            }
            strWorkflowIDs = strWorkflowIDs.Replace("''", "','");

            if (strWorkflowIDs == string.Empty)
            {
                strWorkflowIDs = "''";
            }

            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT(CASE WHEN sb.specname IS NULL THEN sb1.specname ELSE sb.specname END) AS specname,");
            strQuery.AppendLine("   (CASE WHEN s.specrevision IS NULL THEN s1.specrevision ELSE s.specrevision END) AS specrevision,");
            strQuery.AppendLine("   (CASE WHEN s.specid IS NULL THEN s1.specid ELSE s.specid END) AS specid,");
            strQuery.AppendLine("   (CASE WHEN s.teamid IS NULL THEN s1.teamid ELSE s.teamid END) AS teamid,");
            strQuery.AppendLine("   (CASE WHEN s.resourcegroupid IS NULL THEN s1.resourcegroupid ELSE s.resourcegroupid END) AS resourcegroupid,");
            strQuery.AppendLine("   NVL((CASE WHEN s.unitworktime IS NULL THEN s1.unitworktime ELSE s.unitworktime END),1) AS unitworktime,");
            strQuery.AppendLine("   wfs.workflowid,wfs.sequence,wfs.workflowstepid,");
            strQuery.AppendLine("   (CASE WHEN o.worktimexs IS NULL THEN NVL(o1.worktimexs,1) ELSE NVL(o.worktimexs,1) END) AS worktimexs");
            strQuery.AppendLine("FROM workflowstep wfs");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = wfs.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine("LEFT JOIN specbase sb1 ON sb1.specbaseid = wfs.specbaseid");
            strQuery.AppendLine("LEFT JOIN spec s1 ON s1.specid = sb1.revofrcdid");
            strQuery.AppendLine("LEFT JOIN operation o ON o.operationid = s.operationid");
            strQuery.AppendLine("LEFT JOIN operation o1 ON o1.operationid = s1.operationid");
            strQuery.AppendFormat("WHERE wfs.workflowid IN ({0})", strWorkflowIDs);
            strQuery.AppendLine("ORDER BY wfs.sequence ASC");

            DataTable DT = OracleHelper.GetDataTable(strQuery.ToString());
            return DT;
        }
        #endregion

        public string FormatParaStr(string strPara)
        {
            string str = "";
            str = strPara.Replace("'", "").Replace("%", "").Replace("&", "");
            return str;
        }

        #region 工序名称处理
        /// <summary>
        /// 剔除工序名称中的件号
        /// </summary>
        /// <param name="strSpecName"></param>
        /// <returns></returns>
        public string GetSpecNameWithOutProdName(string strSpecName)
        {
            string[] array = strSpecName.Split('-');
            int intLen = array.Length;

            string str = "";
            if (intLen >= 3)
            {
                str = array[intLen - 2].ToString() + "-" + array[intLen - 1].ToString();
            }
            else
            {
                str = strSpecName;
            }

            return str;
        }

        /// <summary>
        /// 从工序名称中获取工序号
        /// </summary>
        /// <param name="strSpecName"></param>
        /// <returns></returns>
        public string GetSpecNoFromSpecName(string strSpecName)
        {
            string[] array = strSpecName.Split('-');
            int intLen = array.Length;

            string str = "";
            //原取值>=3
            if (intLen >= 2)
            {
                str = array[intLen - 2].ToString();
            }
            else
            {
                str = strSpecName;
            }

            return str;
        }

        /// <summary>
        /// 从工序名称中获取真正的工序名称
        /// </summary>
        /// <param name="strSpecName"></param>
        /// <returns></returns>
        public string GetSpecNameFromSpecName(string strSpecName)
        {
            string[] array = strSpecName.Split('-');
            int intLen = array.Length;

            string str = "";
            if (intLen >= 3)
            {
                str = array[intLen - 1].ToString();
            }
            else
            {
                str = strSpecName;
            }

            return str;
        }
        #endregion

        //剔除批次名称中的件号
        public string GetContainerNameWithoutProduct(string strContainerName)
        {
            string[] strArray = strContainerName.Split('/');
            if (strArray.Length >= 2)
            {
                return strArray[strArray.Length - 1];
            }
            else
            {
                return strContainerName;
            }
        }

        public uMESPagingDataDTO PagingData(DataTable dt, int currentPage, int pageSize)
        {

            dt.Columns.Add("rowno", typeof(System.Int32));
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dt.Rows[i]["rowno"] = i + 1;
            }
            uMESPagingDataDTO dto = new uMESPagingDataDTO();
            dto.RowCount = dt.Rows.Count.ToString();

            if (dt.Rows.Count % pageSize != 0)
            {
                dto.PageCount = (dt.Rows.Count / pageSize + 1).ToString();
            }
            else
            {
                dto.PageCount = (dt.Rows.Count / pageSize).ToString();
            }
            int min = (currentPage - 1) * pageSize;
            int max = currentPage * pageSize;

            DataRow[] drs = dt.Select("rowno>" + min + " and rowno<=" + max, "rowno");

            DataTable tmp = dt.Clone();
            foreach (DataRow dr in drs)
            {
                tmp.ImportRow(dr);
            }

            dto.DBTable = tmp;
            return dto;
        }

        public int GetMonthDays(string strMonth)
        {
            DateTime date = Convert.ToDateTime(strMonth);
            int month = date.Month;
            int year = date.Year;


            int days = 0;
            if (month == 1 || month == 3 || month == 5 || month == 7 || month == 8 || month == 10 || month == 12)
            {
                days = 31;
            }
            else if (month == 4 || month == 6 || month == 9 || month == 11)
            {
                days = 30;
            }
            else
            { // 2月份，闰年29天、平年28天  
                if ((year % 4 == 0 && year % 100 != 0) || year % 400 == 0)
                {
                    days = 29;
                }
                else
                {
                    days = 28;
                }
            }

            return days;
        }

        /// <summary>
        /// 获取所有车间名称(factoryname)和描述(description)
        /// </summary>
        public DataTable GetFactoryNames()
        {
            string sql = "select f.factoryid,f.factoryname,f.description from factory f order by f.factoryname asc";
            DataTable dt = OracleHelper.GetDataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取车间描述
        /// </summary>
        public DataTable getFactoryName(string factoryname)
        {
            string sql = "select f.description from factory f";
            sql += string.Format(" where f.factoryname = '{0}' order by f.factoryname asc", factoryname);
            DataTable dt = OracleHelper.GetDataTable(sql);
            return dt;
        }

        public DataTable GetResource(string team)
        {
            string sql =
            "select rd.resourcename,  rd.resourceename\n" +
            "  from resourcedef rd\n" +
            "  left join team t on rd.teamid = t.teamid\n" +
            " where t.teamname = '" + team + "'";
            return OracleHelper.GetDataTable(sql);
        }


        /// <summary>
        ///     将DataTable B 合并至 DataTable A 中,A 与 B 的关系是 N:1, 
        ///     本方法将改变A表的结构,外键列名应在合并前设为相同的名称
        /// </summary>
        /// <param name="columns">两个表之间的外键</param>
        public DataTable CombineDataTale(DataTable A, DataTable B, List<string> columns)
        {
            //将B中的新列添加到A中
            for (int i = 0; i < B.Columns.Count; i++)
            {
                if (columns.Contains(B.Columns[i].ColumnName.ToLower()) == false)
                {
                    A.Columns.Add(B.Columns[i].ColumnName, Type.GetType("System.String"));
                }
            }
            //将外键对应的行从B复制到A中
            StringBuilder sb = null;
            DataRow[] drs = null;
            for (int i = 0; i < A.Rows.Count; i++)
            {
                sb = new StringBuilder();
                for (int j = 0; j < columns.Count; j++)
                {
                    sb.AppendFormat("{0} = '{1}' and ", columns[j], A.Rows[i][columns[j]]);
                }
                if (sb.Length > 0)
                {
                    sb.Remove(sb.Length - 5, 5);
                }
                drs = B.Select(sb.ToString());
                if (drs.Length > 0)
                {
                    for (int k = 0; k < B.Columns.Count; k++)
                    {
                        A.Rows[i][B.Columns[k].ColumnName] = drs[0][k].ToString();
                    }
                }
            }
            return A;
        }
    }
}
